package itsm.isperp.module.service.configuration;

import itsm.isperp.framework.core.context.ContextHolder;
import itsm.isperp.framework.service.BaseService;
import itsm.isperp.module.entity.configuration.ConfCiUseRelation;
import itsm.isperp.module.repository.configuration.ConfCiUseRelationMapper;
import itsm.isperp.module.service.project.ProjProjectService;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

/**
 * 资产使用记录表服务
 * 
 */
@Service
public class ConfCiUseRelationService extends BaseService<ConfCiUseRelation> {

	@Autowired
	protected ConfCiUseRelationMapper confCiUseRelationMapper;

	@Autowired
	protected JdbcTemplate jdbcTemplate;

	@Override
	public ConfCiUseRelationMapper getRepository() {
		return this.confCiUseRelationMapper;
	}

	public void deleteCiRelationAndChangeRelateProperty(String changeId,
			String ciId, String relationType) {
		String sql = "DELETE FROM conf_ci_use_relation WHERE relation_type='"
				+ relationType + "' AND relation_id='" + changeId
				+ "' AND ci_id='" + ciId + "'";
		String sql2 = "DELETE FROM chg_change_configuration WHERE change_id='"
				+ changeId + "' AND ci_id='" + ciId + "'";
		String sql3 = "delete from conf_ci_relation_plan where change_id='"
				+ changeId + "' AND ci_id='" + ciId + "'";
		jdbcTemplate.batchUpdate(sql, sql2, sql3);
	}

	public void deleteByRelationIdAndCiId(String relationId, String ciId) {
		String sql = "delete from conf_ci_use_relation where relation_id='"
				+ relationId + "' and ci_id='" + ciId + "'";
		jdbcTemplate.execute(sql);

	}

	public void deleteByRelationId(String relationId, String ciId) {
		String sql = "delete from conf_ci_use_relation where relation_id='"
				+ relationId + "'";
		jdbcTemplate.execute(sql);

	}

	public void insertIntoChgRelateCiMul(String id, String changeId,
			String categoryId, String ciIds) {
		String sql = "insert into CHG_RELATE_CI_MUL(ID,CI_IDS,CHANGE_ID,CI_CATEGORY_ID) VALUES('"
				+ id
				+ "','"
				+ ciIds
				+ "','"
				+ changeId
				+ "','"
				+ categoryId
				+ "')";
		jdbcTemplate.execute(sql);

	}

	public Map<String, Object> getChgRelateCiMul(String id) {
		String sql = "select a.*,b.treenode_name_path as conf_category_name,(length(ci_ids)-length(replace(ci_ids,',',''))+1) as countnum FROM CHG_RELATE_CI_MUL a left join conf_category  b on a.CI_CATEGORY_ID=b.id where a.id='"
				+ id + "'";
		return jdbcTemplate.queryForMap(sql);
	}

	public List<Map<String, Object>> getChgRelateCiMulByChangeId(String id) {
		String sql = "select a.*,b.treenode_name_path as conf_category_name,(length(ci_ids)-length(replace(ci_ids,',',''))+1) as countnum FROM CHG_RELATE_CI_MUL a left join conf_category  b on a.CI_CATEGORY_ID=b.id where a.change_id='"
				+ id + "'";
		return jdbcTemplate.queryForList(sql);
	}

	public void deleteChgConfuguration(String changeId) {
		String sql1 = "delete from conf_ci_use_relation where relation_id='"
				+ changeId + "' and relation_type='changeProcess'";
		String sql2 = "delete from chg_change_configuration where change_id='"
				+ changeId + "'";
		String sql3 = "delete from chg_change_configuration_mul where change_id='"
				+ changeId + "'";
		String sql4 = "delete from chg_relate_ci_mul where change_id='"
				+ changeId + "'";
		jdbcTemplate.batchUpdate(sql1, sql2, sql3, sql4);

	}

	public void deleteChgConfugurationMulByRelationId(String relationId,
			String relationType) {
		String inIds = "";
		String sql0 = "SELECT *FROM chg_relate_ci_mul where id='" + relationId
				+ "'";
		Map<String, Object> m = jdbcTemplate.queryForMap(sql0);
		String ciIds = m.get("ci_ids").toString();
		String changeId = m.get("change_id").toString();
		String[] tarr = ciIds.split(",");
		for (int i = 0; i < tarr.length; i++) {
			inIds += "'" + tarr[i] + "',";
		}
		inIds = inIds.substring(0, inIds.length() - 1);

		String sql1 = "delete from conf_ci_use_relation where relation_id='"
				+ changeId + "' and relation_type='" + relationType
				+ "' and ci_id in(" + inIds + ")";
		String sql2 = "delete from chg_change_configuration where change_id='"
				+ changeId + "' and ci_id in(" + inIds + ")";
		String sql3 = "delete from chg_change_configuration_mul where change_id='"
				+ changeId + "' and relation_id='" + relationId + "'";
		String sql4 = "delete from chg_relate_ci_mul where id='" + relationId
				+ "'";
		jdbcTemplate.batchUpdate(sql1, sql2, sql3, sql4);

	}

}
